package com.ym.b2b.order.xc.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.ym.b2b.commons.conf.WebProviderTokenUser;
import com.ym.b2b.commons.exception.BaseException;
import com.ym.b2b.order.sqlbean.EmSOrderConfirmItem;
import com.ym.b2b.order.vo.OrderInfoVO;
import com.ym.b2b.order.xc.XCDBFactroy;
import com.ym.b2b.order.xc.XcService;
import com.ym.b2b.order.xc.sqlbean.C1n_hz;
import com.ym.b2b.order.xc.sqlbean.C1n_mx;
import com.ym.b2b.order.xc.sqlbean.DataPackageERP;
import com.ym.b2b.order.xc.sqlbean.Jsonjxd;
import com.ym.b2b.order.xc.sqlbean.XCReturnModel;
import com.ym.b2b.order.xc.sqlbean.XcSaleDetail;
import com.ym.b2b.order.xc.sqlbean.XcStock;
import com.ym.b2b.sdk.request.Request;
import com.ym.b2b.sdk.response.Response;
import com.zc.commons.base.Page;
import com.zc.commons.base.Result;
import com.zc.commons.service.ZcService;

@Service
public class XcServiceImpl extends ZcService implements XcService {
	
    // 下订单
    @Override
    public Result dealOrder(OrderInfoVO vo) throws BaseException {
    	Result ret = new Result();
        String jsonjxd = makeJsonForCreate(vo);
        Map<String, String> map = new HashMap<String, String>();
		map.put("jsoninf", jsonjxd);
		
		logger.info(" ------ERP 下订单接口传给ERP的数据:\n" + jsonjxd);
        Request req = new Request("http://WINDOWS-OAS41VM:99/KSOABusiness.asmx/CreateOrderAndOrderItems", map);
        Response res = req.execute();
        String returnString = res.getResponseBody();
		String str = returnString.substring(returnString.indexOf("{"), returnString.indexOf("}")+1 );
		XCReturnModel returnModel = JSON.parseObject(str, XCReturnModel.class);
		logger.info(" ------ERP 下订单接口返回的结果: \n" + JSON.toJSONString(returnModel));
		if(!"200".equals(returnModel.getCode())){
            ret.setCodeResult(Result.CODE_100, returnModel.getCode());
		}else{
			ret.putData("orderRet", returnModel);
		}
        return ret;
    }

    // 取消订单
    @Override
    public Result cancelOrder(OrderInfoVO vo) throws BaseException {
		Result ret = new Result();
		C1n_hz c1n_hz = new C1n_hz();	
		c1n_hz.setErpdjbhkey(vo.getErpCode());
		Map<String, String> map = new HashMap<String, String>();
		map.put("jsoninf", JSON.toJSONString(c1n_hz));
		
		logger.info(" ------ERP 下订单接口传给ERP的数据:\n" + JSON.toJSONString(c1n_hz));
		Request req = new Request("http://WINDOWS-OAS41VM:99/KSOABusiness.asmx/DeleteOrder", map);
		Response res = req.execute();
		String returnString = res.getResponseBody();
		String str = returnString.substring(returnString.indexOf("{"), returnString.indexOf("}")+1 );
		XCReturnModel returnModel = JSON.parseObject(str, XCReturnModel.class);
		logger.info(" ------ERP 下订单接口返回的结果: \n" + JSON.toJSONString(returnModel));
		
		if(!"200".equals(returnModel.getCode())){
            ret.setCodeResult(Result.CODE_100, ret.getResult());
		}else{
			ret.putData("orderRet", returnModel);
		}
        return ret;
    }

    // 更新订单
    @Override
    public void updateOrder(OrderInfoVO vo, List<EmSOrderConfirmItem> list) throws BaseException {
    	String jsonjxd = makeJsonForUpdate(vo, list);
        Map<String, String> map = new HashMap<String, String>();
		map.put("jsoninf", jsonjxd);
		logger.info(" ------ERP 更新订单接口传给ERP的数据:\n" + jsonjxd);
        Request req = new Request("http://WINDOWS-OAS41VM:99/KSOABusiness.asmx/UpdateOrderAndOrderItems", map);
        Response res = req.execute();
        String returnString = res.getResponseBody();
		String str = returnString.substring(returnString.indexOf("{"), returnString.indexOf("}")+1 );
		XCReturnModel returnModel = JSON.parseObject(str, XCReturnModel.class);
		logger.info(" ------ERP 更新订单接口返回的结果: \n" + JSON.toJSONString(returnModel));
}
    
    /**
      * <p>Description: 根据订单的erpCode查询获得该订单产品的 质检单和首营资料</p>
      * <p>Title: getDataPackageERPList</p>
      * @param erpCode
      * @see com.ym.b2b.order.xc.XcService#getDataPackageERPList(java.lang.String)
     */
	@Override
	public List<DataPackageERP> getDataPackageERPList(String erpCode) throws BaseException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<DataPackageERP> list = new ArrayList<DataPackageERP>();
		try {
			conn = XCDBFactroy.getXcCon();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
		} catch (Exception e) {
			throw new BaseException(e.getMessage());
		}
		String sql = "SELECT a.spbh, ltrim(rtrim(a.spmch)), a.shpgg, a.shengccj, b.djbh, b.pihao2, c.folderpath, ltrim(rtrim(c.imageid)), c.extend, c.leibie "
				+ " from spkfk a,pf_ckmx b,ImageDoc c " 
				+ "	where a.spid=b.spid and b.spid=c.detailid and b.pihao2=c.fzxx1 "
				+ " and b.djbh like'%" + erpCode + "%'";
		System.out.println(sql);
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				DataPackageERP data = new DataPackageERP();
				data.setSpbh(rs.getString(1));
				data.setSpmch(rs.getString(2));
				data.setShpgg(rs.getString(3));
				data.setShengccj(rs.getString(4));
				data.setDjbh(rs.getString(5));
				data.setPihao2(rs.getString(6));
				data.setFolderpath(rs.getString(7));
				data.setImageid(rs.getString(8));
				data.setExtend(rs.getString(9));
				data.setLeibie(rs.getString(10));
				list.add(data);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
                if (stmt != null) {
                    stmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                throw new BaseException("关闭ERP数据库连接异常:" + e.getMessage());
            }
		}
		return list;
	}
    
	/**
	  * <p>Description:前台供应商查销售流向 </p>
	  * <p>Title: searchPageSalesDetail</p>
	  * @param emSSaleDetail
	  * @param page
	  */
	@Override
	public List<XcSaleDetail> searchXcPageSalesDetail(XcSaleDetail saleDetail, Page<XcSaleDetail> page, int type)
			throws BaseException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<XcSaleDetail> list = new ArrayList<XcSaleDetail>();
		try {
			conn = XCDBFactroy.getXcProviderCon();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
		} catch (Exception e) {
			throw new BaseException(e.getMessage());
		}
		
		String sql = "SELECT b.rq, d.dwmch, a.spmch, a.shpgg, a.shengccj, a.dw, c.pihao2, c.sxrq, c.shl, b.beizhu "
				+ " FROM spkfk a, ";
		if(type ==1){
			sql+= " pf_ckhz b, pf_ckmx c, mchk d ";
		}else if (type ==2){
			sql+= " jh_rkhz b, jh_rkmx c, mchk d";
		}
		sql+= " where a.spid=c.spid "
				+ "and b.djbh=c.djbh "
				+ "and b.dwbh=d.dwbh "
				+ "and a.spbh in (" + saleDetail.getSpbhs() + ") "
				+ "and b.rq >= '" + saleDetail.getStartRq() + "' ";
				
		if(saleDetail.getEndRq() != null && !"".equals(saleDetail.getEndRq())){
			sql += "and b.rq <= '" + saleDetail.getEndRq() + "' ";
		}
		sql +=  "order by b.rq desc " ;
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				XcSaleDetail data = new XcSaleDetail();
				data.setRq(rs.getString(1).trim());
				data.setDwmch(rs.getString(2).trim());
				data.setSpmch(rs.getString(3).trim());
				data.setShpgg(rs.getString(4).trim());
				data.setShengccj(rs.getString(5).trim());
				data.setDw(rs.getString(6).trim());
				data.setPihao2(rs.getString(7).trim());
				data.setSxrq(rs.getString(8).trim());
				data.setShl(rs.getString(9).trim());
				data.setBeizhu(rs.getString(10).trim());
				list.add(data);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
                if (stmt != null) {
                    stmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new BaseException("关闭ERP数据库连接异常:" + e.getMessage());
            }
		}
		return list;
	}


	/**
	  * <p>Title: searchXcPageStock</p>
	  * <p>Description: 前台供应商查产品库存</p>
	  * @param xcStock
	  * @param page
	  */
	@Override
	public List<XcStock> searchXcPageStock(XcStock xcStock, Page<XcStock> page) throws BaseException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<XcStock> list = new ArrayList<XcStock>();
		try {
			conn = XCDBFactroy.getXcProviderCon();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
		} catch (Exception e) {
			throw new BaseException(e.getMessage());
		}
		
		String sql = "SELECT a.spmch, a.shpgg, a.shengccj, a.dw, a.lshpj, b.pihao2, b.sxrq, b.shl  "
				+ " FROM spkfk a,sphwph b"
				+ " where a.spid=b.spid "
				+ " and b.shl<>0 "
				+ "and a.spbh in (" + xcStock.getSpbhs() + ") ";
		if (xcStock.getSpbh() != null && !"".equals(xcStock.getSpbh().trim())) {
			sql += " and a.spbh = '" + xcStock.getSpbh().trim() + "'";
		}
		if (xcStock.getSpmch() != null && !"".equals(xcStock.getSpmch().trim())) {
			sql += " and a.spmch like '%" + xcStock.getSpmch().trim() + "%'";
		}
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				XcStock data = new XcStock();
				data.setSpmch(rs.getString(1).trim());
				data.setShpgg(rs.getString(2).trim());
				data.setShengccj(rs.getString(3).trim());
				data.setDw(rs.getString(4).trim());
				data.setLshpj(rs.getString(5).trim());
				data.setPihao2(rs.getString(6).trim());
				data.setSxrq(rs.getString(7).trim());
				data.setShl(rs.getString(8).trim());
				list.add(data);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
                if (stmt != null) {
                    stmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new BaseException("关闭ERP数据库连接异常:" + e.getMessage());
            }
		}
		return list;
	}
	
    private String makeJsonForCreate(OrderInfoVO vo){
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");
    	Jsonjxd jsonjxd = new Jsonjxd();						// 	订单Model
        C1n_hz c1n_hz = new C1n_hz();							// 	订单汇总
        List<C1n_mx> jsonjxddmx = new ArrayList<C1n_mx>();		//	订单明细
        
        c1n_hz.setDjbh(vo.getOrderCode().substring(6));						//  单据编号
        c1n_hz.setRq(sdf.format(new Date()));					//	日期
        c1n_hz.setOntime(sdf2.format(new Date()));				//	时间
        c1n_hz.setDwbh(vo.getUser().getBuyerCode());			//  单位内码
        c1n_hz.setYwy(vo.getSalesman());						//	业务员
        
        // ------------------	??????	------------------
        c1n_hz.setDjbs("XSF");									// 	djbs单据的标识 (固定写死)
        c1n_hz.setBm("宣城医药");									//	bm 部门名称
        c1n_hz.setShenhe("是");									//	shenhe是否审核 (固定写死)
        c1n_hz.setIs_zx("否");									//	is_zx是否执行(固定写死)
        c1n_hz.setIs_shouh("否");								//	is_shouh是否收货(固定写死)
        c1n_hz.setShplx("P");									//	shplx税票类型(固定写死)
        c1n_hz.setShenhe_yw("否");								//	shenhe_yw业务审核(固定写死)
        c1n_hz.setShenhe_ty("否");								//	shenhe_ty特药审核(固定写死)
        c1n_hz.setShenhe_ty("否");								//	shenhe_ty特药审核(固定写死)
        c1n_hz.setIs_fk("否"); 									//	是否付款
//        c1n_hz.setKaiprq(sdf.format(new Date())); 				//	kaiprq制单日期
//        c1n_hz.setYdhrq(sdf.format(new Date())); 					//	ydhrq预到货日期
//        c1n_hz.setShenhrq(sdf.format(new Date()));				//	shenhrq审核日期
//        c1n_hz.setYansrq(sdf.format(new Date())); 				//	yansrq验收日期
//        c1n_hz.setYckrq(sdf.format(new Date())); 					//  yckrq原出库日期
//        c1n_hz.setShenhrq_yw(sdf.format(new Date()));				//	shenhrq_yw业务审核日期
//        c1n_hz.setShenhrq_ty(sdf.format(new Date()));				//	shenhrq_ty特药审核日期
        
		for (int i = 0; i < vo.getEmSOrderConfirmItemList().size(); i++) {
			EmSOrderConfirmItem item = vo.getEmSOrderConfirmItemList().get(i);
			C1n_mx mx = new C1n_mx();
			mx.setDjbh(vo.getOrderCode().substring(6));
			mx.setDj_sn(String.valueOf(i + 1));
			mx.setIs_jush("否");									// 是否拒收
			mx.setIs_shouh("否");								// 是否收货
			mx.setSpid(item.getProductCode()); // 商品编码
			mx.setShl(String.valueOf(item.getConfirmAmount()));	// 数量
			mx.setDj(String.valueOf(item.getSalePrice()));		// 单价
			mx.setJe(String.valueOf(item.getConfirmMoney()));	// 金额
			mx.setPihao(item.getBatchNumber());
			mx.setPihao2(item.getBatchNumber());
			jsonjxddmx.add(mx);
		}
		jsonjxd.setJsonjxddhz(c1n_hz);
		jsonjxd.setJsonjxddmx(jsonjxddmx);
    	return JSON.toJSONString(jsonjxd);
    }
    
    private String makeJsonForUpdate(OrderInfoVO vo, List<EmSOrderConfirmItem> list){
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");
    	Jsonjxd jsonjxd = new Jsonjxd();						// 	订单Model
        C1n_hz c1n_hz = new C1n_hz();							// 	订单汇总
        List<C1n_mx> jsonjxddmx = new ArrayList<C1n_mx>();		//	订单明细

        c1n_hz.setDjbh(vo.getOrderCode().substring(6));
        c1n_hz.setRq(sdf.format(new Date()));					//	日期
        c1n_hz.setOntime(sdf2.format(new Date()));				//	时间
        c1n_hz.setDwbh(vo.getBuyerCode());						//  单位内码
        c1n_hz.setYwy(vo.getSalesman());						//	业务员
        
        c1n_hz.setDjbs("XSF");									// 	djbs单据的标识 (固定写死)
        c1n_hz.setBm("宣城医药");									//	bm 部门名称
        c1n_hz.setShenhe("是");									//	shenhe是否审核 (固定写死)
        c1n_hz.setIs_zx("否");									//	is_zx是否执行(固定写死)
        c1n_hz.setIs_shouh("否");								//	is_shouh是否收货(固定写死)
        c1n_hz.setShplx("P");									//	shplx税票类型(固定写死)
        c1n_hz.setShenhe_yw("否");								//	shenhe_yw业务审核(固定写死)
        c1n_hz.setShenhe_ty("否");								//	shenhe_ty特药审核(固定写死)
        c1n_hz.setIs_fk("是"); 									//	是否付款
        
        for (int i = 0; i < list.size(); i++) {
			EmSOrderConfirmItem item = list.get(i);
			C1n_mx mx = new C1n_mx();
			mx.setDjbh(vo.getOrderCode().substring(6));
			mx.setDj_sn(String.valueOf(i + 1));
			mx.setIs_jush("否");									// 是否拒收
			mx.setIs_shouh("否");								// 是否收货
			mx.setSpid(item.getProductCode()); // 商品编码
			mx.setShl(String.valueOf(item.getConfirmAmount()));	// 数量
			mx.setDj(String.valueOf(item.getSalePrice()));		// 单价
			mx.setJe(String.valueOf(item.getConfirmMoney()));	// 金额
			mx.setPihao(item.getBatchNumber());
			mx.setPihao2(item.getBatchNumber());
			jsonjxddmx.add(mx);
		}
        jsonjxd.setJsonjxddhz(c1n_hz);
		jsonjxd.setJsonjxddmx(jsonjxddmx);
		jsonjxd.setErpdjbhkey(vo.getErpCode());
    	return JSON.toJSONString(jsonjxd);
    }

}
